#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2014年12月12日

@author: chenyongbing
'''
import sys,os,datetime
import calendar
current_dir = os.path.dirname(__file__)




import logging


sys.path.append(os.path.join(current_dir,'../..'))

from base.PyEmail import *

from base.localConfig import baseLocalConfig


from base.retail_datas import myRetailDatas


from base.zhimind_ws_datas import ZhimindWsDatas
myZhimindWsDatas = ZhimindWsDatas(base='http://zm04:8080/zm-ws-3.0/api/v3/')

import openpyxl
class ExportData(object):
    '''
    classdocs
    '''


    def __init__(self,name='day',sheetConfig=None,testing=False):
        '''
        Constructor
        '''
        if sheetConfig == None:
            sheetConfig = {'sheet1':{'row':1,'col':1},'sheet2':{'row':1,'col':1}}
        self.sheetConfig = sheetConfig
        self.testing = testing
    def get_region_groups(self,root,name=""):
        groups = []
        for snode in root.findall('.//region[@name="%s"]'%name):
            for gnode in snode.findall('.//group'):
                tag = gnode.tag
                if tag!='group':continue
                group = gnode.get('id')
                groups.append(group)
        return groups
    
    
    def get_last_st_et_by_timeType(self,startTime,timeType,range='all',type='last'):
        u'''通过time及timeType获取上一个周期的st et
            range:all 周期的 全部
            range:end 截止到 st
            range:start 从st到末尾
        '''
        if timeType == 'day' or timeType==1:
            st = startTime[:10]
            et = st
            last_st = last_et = (datetime.date(int(st[:4]),int(st[5:7]),int(st[8:10])) - datetime.timedelta(1)).strftime('%Y-%m-%d')
        elif timeType == 'week' or timeType ==2:
            wk = datetime.date(int(startTime[:4]),int(startTime[5:7]),int(startTime[8:10])).weekday()
            if range in ('all','end'):
                st = (datetime.date(int(startTime[:4]),int(startTime[5:7]),int(startTime[8:10])) - datetime.timedelta(wk)).strftime('%Y-%m-%d')
            elif range in ('start'):
                st = startTime
                
            if range in ('all','start'):
                et = (datetime.date(int(st[:4]),int(st[5:7]),int(st[8:10])) + datetime.timedelta(6)).strftime('%Y-%m-%d')
            elif range in ('end'):
                et = startTime
            last_st = (datetime.date(int(st[:4]),int(st[5:7]),int(st[8:10])) - datetime.timedelta(7)).strftime('%Y-%m-%d')
            last_et = (datetime.date(int(et[:4]),int(et[5:7]),int(et[8:10])) - datetime.timedelta(7)).strftime('%Y-%m-%d')
        elif timeType == 'month' or timeType==3:
            if range in ('all','end'):
                st = startTime[:7]+'-01'
            elif range in ('start'):
                st = startTime
            if range in ('all','start'):
                et = st[:7] + '-'+ str(calendar.monthrange(int(st[:4]), int(st[5:7]))[1])
            elif range in ('end'):
                et = startTime
            month_st =     startTime[:7]+'-01'
            
            last_month_et = (datetime.date(int(st[:4]),int(st[5:7]),int(st[8:10])) - datetime.timedelta(1)).strftime('%Y-%m-%d')
            last_month_et_day = last_month_et[8:10]
            startTime_day = startTime[8:10]
            if range in ('all','end'):
                if int(startTime_day)>int(last_month_et_day):
                    last_et = last_month_et
                else:
                    last_et = last_month_et[:7]+'-'+startTime_day
                last_st = last_et[:7]+'-01'
            elif range in ('start'):
                if int(startTime_day)>int(last_month_et_day):
                    print startTime_day,last_month_et_day
                    logging.error('day error')
                    sys.exit()
                else:
                    last_st = last_month_et[:7]+'-'+startTime_day
                    last_et = last_month_et
        elif timeType=='quarter' or timeType==4:
            month = str(int((int(startTime[5:7])-1)/3)*3+1)
            if len(month)==1:
                month = '0'+month
            st = startTime[:4] +'-'+ month + '-01'
            #print st
            et = (datetime.date(int(st[:4]),int(int(st[5:7])+4),1) - datetime.timedelta(1)).strftime('%Y-%m-%d')
            last_st = st
            last_et = et
        if type=='last':
            return last_st,last_et
        else:
            return st,et
    
    def get_st_et_by_timeType(self,startTime,timeType,range='all'):
        u'''通过time及timeType获取本周期的st et'''
        return self.get_last_st_et_by_timeType(startTime, timeType=timeType, range=range, type='now')
        
    
    def analyze_template_file(self,name='tencent',day=None,export_file=''):
        
        if re.search('day',name):
            #print 'day'
            self.analyze_template_file_day_report_template(name=name,day=day,export_file=export_file)
        elif re.search('month',name):
            #print 'month'
            self.analyze_template_file_month_report_template(name, day, export_file)
    def get_customer_inside_info(self,ws,start_row=1,start_col=1,day=None,comp_group=None,group=None,groupType=4):
        '''入店情况'''
        last_day,last_day = self.get_last_st_et_by_timeType(day, timeType='day')
        last_week_st,last_week_et = self.get_last_st_et_by_timeType(day, timeType='week')
        last_month_st,last_month_et = self.get_last_st_et_by_timeType(day, timeType='month')
        #日进店量 col 2;日进店环比 3
        day_count,day_change = myZhimindWsDatas.get_customer(startTime=last_day, endTime=last_day, comp_group=comp_group, group=group,groupType=groupType)
        if day_change!='-':
            day_change = str(day_change*100)+'%'
        ws.cell(row=start_row,column=start_col).value = day_count
        ws.cell(row=start_row,column=start_col+1).value = day_change
         
        #上周进店量 col 4;上周进店环比 5
        last_week_count,last_week_change = myZhimindWsDatas.get_customer(startTime=last_week_st, endTime=last_week_et, comp_group=comp_group, group=group,groupType=groupType)
        if last_week_change!='-':
            last_week_change = str(last_week_change*100)+'%'
        ws.cell(row=start_row,column=start_col+2).value = last_week_count
        ws.cell(row=start_row,column=start_col+3).value = last_week_change
         
        #上月进店量 col 6;上月进店环比 7
        last_month_count,last_month_change = myZhimindWsDatas.get_customer(startTime=last_month_st, endTime=last_month_et, comp_group=comp_group, group=group,groupType=groupType)
        if last_month_change!='-':
            last_month_change = str(last_month_change*100)+'%'
        ws.cell(row=start_row,column=start_col+4).value = last_month_count
        ws.cell(row=start_row,column=start_col+5).value = last_month_change    
    
    def get_customer_dur_info(self,ws,start_row=1,start_col=1,day=None,comp_group=None,group=None,groupType=4):
        last_day,last_day = self.get_last_st_et_by_timeType(day, timeType='day')
        last_last_day,last_last_day = self.get_last_st_et_by_timeType(last_day, timeType='day')
        last_week_st,last_week_et = self.get_last_st_et_by_timeType(day, timeType='week')
        last_last_week_st,last_last_week_et = self.get_last_st_et_by_timeType(last_week_st, timeType='week')
        last_month_st,last_month_et = self.get_last_st_et_by_timeType(day, timeType='month')
        last_last_month_st,last_last_month_et = self.get_last_st_et_by_timeType(last_month_st, timeType='month')
        #日平均滞留时长 col 8;日平均滞留时长环比 9
        avg_dur = myZhimindWsDatas.get_customer_dur(startTime=last_day, endTime=last_day, comp_group=comp_group, group=group,groupType=groupType)
        last_avg_dur = myZhimindWsDatas.get_customer_dur(startTime=last_last_day, endTime=last_last_day, comp_group=comp_group, group=group,groupType=groupType)
        if last_avg_dur==0:
            avg_change = '-'
        else:
            avg_change = str(round((last_avg_dur-avg_dur)/float(last_avg_dur),4)*100)+'%'
            
        ws.cell(row=start_row,column=start_col).value = round(avg_dur/float(60),1)
        ws.cell(row=start_row,column=start_col+1).value = avg_change
        
        #上周平均滞留时长 col 10;上周平均滞留时长环比 11
        last_week_avg_dur = myZhimindWsDatas.get_customer_dur(startTime=last_week_st, endTime=last_week_et, comp_group=comp_group, group=group,groupType=groupType)
        last_last_week_avg_dur = myZhimindWsDatas.get_customer_dur(startTime=last_last_week_st, endTime=last_last_week_et, comp_group=comp_group, group=group,groupType=groupType)
        if last_last_week_avg_dur==0:
            last_week_avg_change = '-'
        else:
            last_week_avg_change = str(round((last_last_week_avg_dur-last_week_avg_dur)/float(last_last_week_avg_dur),4)*100)+'%'
            
        ws.cell(row=start_row,column=start_col+2).value = round(last_week_avg_dur/float(60),1)
        ws.cell(row=start_row,column=start_col+3).value = last_week_avg_change
        #上月平均滞留时长 col 12;上月平均滞留时长环比 13
        last_month_avg_dur = myZhimindWsDatas.get_customer_dur(startTime=last_month_st, endTime=last_month_et, comp_group=comp_group, group=group,groupType=groupType)
        last_last_month_avg_dur = myZhimindWsDatas.get_customer_dur(startTime=last_last_month_st, endTime=last_last_month_et, comp_group=comp_group, group=group,groupType=groupType)
        if last_last_month_avg_dur==0:
            last_month_avg_change = '-'
        else:
            last_month_avg_change = str(round((last_last_month_avg_dur-last_month_avg_dur)/float(last_last_month_avg_dur),4)*100)+'%'
            
        ws.cell(row=start_row,column=start_col+4).value = round(last_month_avg_dur/float(60),1)
        ws.cell(row=start_row,column=start_col+5).value = last_month_avg_change    
    
    def get_deep_out_customer_info(self,ws,start_row=1,start_col=1,day=None,comp_group=None,group=None,groupType=4):
        last_day,last_day = self.get_last_st_et_by_timeType(day, timeType='day')
        last_week_st,last_week_et = self.get_last_st_et_by_timeType(day, timeType='week')
        last_month_st,last_month_et = self.get_last_st_et_by_timeType(day, timeType='month')
        #深访、跳出顾客 日访问量
        deep_customer_count,deep_customer_count_change,out_customer_count,out_customer_count_change = myZhimindWsDatas.get_customer_dur(startTime=last_day, endTime=last_day, comp_group=comp_group, prov_group=group,rtype='deep_out',groupType=groupType)
        if deep_customer_count_change!='-':
            deep_customer_count_change = str(deep_customer_count_change*100)+'%'
        if out_customer_count_change!='-':
            out_customer_count_change = str(out_customer_count_change*100)+'%'  
        ws.cell(row=start_row,column=start_col).value = deep_customer_count
        ws.cell(row=start_row,column=start_col+1).value = deep_customer_count_change
        
        ws.cell(row=start_row,column=start_col+6).value = out_customer_count
        ws.cell(row=start_row,column=start_col+7).value = out_customer_count_change
        
        
        #深访、跳出顾客 上周访问量
        l_w_d_c_count,l_w_d_c_change,l_w_o_c_count,l_w_o_c_change = myZhimindWsDatas.get_customer_dur(startTime=last_week_st, endTime=last_week_et, comp_group=comp_group, prov_group=group,rtype='deep_out',groupType=groupType)
        if l_w_d_c_change!='-':
            l_w_d_c_change = str(l_w_d_c_change*100)+'%' 
        if l_w_o_c_change!='-':
            l_w_o_c_change = str(l_w_o_c_change*100)+'%' 
        
        ws.cell(row=start_row,column=start_col+2).value = l_w_d_c_count
        ws.cell(row=start_row,column=start_col+3).value = l_w_d_c_change
        
        ws.cell(row=start_row,column=start_col+8).value = l_w_o_c_count
        ws.cell(row=start_row,column=start_col+9).value = l_w_o_c_change
       
        #深访、跳出顾客 上月访问量
        l_m_d_c_count,l_m_d_c_change,l_m_o_c_count,l_m_o_c_change = myZhimindWsDatas.get_customer_dur(startTime=last_month_st, endTime=last_month_et, comp_group=comp_group, prov_group=group,rtype='deep_out',groupType=groupType)
        
        if l_m_d_c_change!='-':
            l_m_d_c_change = str(l_m_d_c_change*100)+'%' 
        if l_m_o_c_change!='-':
            l_m_o_c_change = str(l_m_o_c_change*100)+'%' 
        ws.cell(row=start_row,column=start_col+4).value = l_m_d_c_count
        ws.cell(row=start_row,column=start_col+5).value = l_m_d_c_change
        
        ws.cell(row=start_row,column=start_col+10).value = l_m_o_c_count
        ws.cell(row=start_row,column=start_col+11).value = l_m_o_c_change
    
    
    def get_customer_month_freq(self,ws,start_row=1,start_col=1,day=None,comp_group=None,group=None,groupType=4):
        week_st,week_et = self.get_st_et_by_timeType(day, timeType='week')
        month_st,month_et = self.get_st_et_by_timeType(day, timeType='month')
        #当月进店次数 14，15，16，17
        count_1,count_2,count_3,count_4 = myZhimindWsDatas.stats_visit_frequency_scatter(startTime=month_st, endTime=month_et, comp_group=comp_group, group=group,groupType=groupType)
        ws.cell(row=start_row,column=start_col).value = count_1
        ws.cell(row=start_row,column=start_col+1).value = count_2
        ws.cell(row=start_row,column=start_col+2).value = count_3
        ws.cell(row=start_row,column=start_col+3).value = count_4
        
        
    def get_customer_month_week_data(self,ws,start_row=1,start_col=1,day=None,comp_group=None,group=None,groupType=4):
        #当月进店情况 18，19
        month_st,month_et = self.get_st_et_by_timeType(day, timeType='month')
        weekday_count,weekend_count = myZhimindWsDatas.get_customer(startTime=month_st, endTime=month_et, comp_group=comp_group, group=group,timeType='week',groupType=groupType)
        month_st,month_et = self.get_st_et_by_timeType(day, timeType='month')
        ws.cell(row=start_row,column=start_col).value = weekday_count
        ws.cell(row=start_row,column=start_col+1).value = weekend_count
    
    
    
    def get_customer_info_for_month_report_template(self,ws,start_row=1,start_col=1,day=None,group=None,groupType=4,customerType='all'):
        '''
            customerType == deep 深访
            customerType == all  所有顾客
        '''
        
        last_day,last_day = self.get_last_st_et_by_timeType(day, timeType='day')
        last_last_day,last_last_day = self.get_last_st_et_by_timeType(last_day, timeType='day')
        #last day 本周
        last_day_week_st,last_day_week_et = self.get_st_et_by_timeType(last_day, timeType='week',range='end')
        #last day 上周
        last_day_last_week_st,last_day_last_week_et = self.get_last_st_et_by_timeType(last_day, timeType='week',range='end')
        #last day 本月
        last_day_month_st,last_day_month_et = self.get_st_et_by_timeType(last_day, timeType='month',range='end')
        #last day 上月
        last_day_last_month_st,last_day_last_month_et = self.get_last_st_et_by_timeType(last_day_month_st, timeType='month',range='start')

        if customerType == 'out':
            #跳出 昨天进店量
            l_d_c,l_d_c_c  = myZhimindWsDatas.get_customer(startTime=last_day_month_st, endTime=last_day_month_et, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
            ws.cell(row=start_row,column=start_col).value = l_d_c
            return 
        
        if customerType=='deep':
            customerType='out'
        
        #前天进店量
        l_l_d_c,l_l_d_c_c  = myZhimindWsDatas.get_customer(startTime=last_day_last_month_st, endTime=last_day_last_month_et, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
        if customerType=='out':
            ws.cell(row=start_row,column=start_col).value = ws.cell(row=start_row,column=start_col-3).value - l_l_d_c
        else:
            ws.cell(row=start_row,column=start_col).value = l_l_d_c
        #昨天进店量
        l_d_c,l_d_c_c  = myZhimindWsDatas.get_customer(startTime=last_day_month_st, endTime=last_day_month_et, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
        if customerType=='out':
            ws.cell(row=start_row,column=start_col+1).value =ws.cell(row=start_row,column=start_col-2).value - l_d_c
        else:
            ws.cell(row=start_row,column=start_col+1).value = l_d_c
    
      
    
    def get_customer_info_for_3(self,ws,start_row=1,start_col=1,day=None,group=None,groupType=4,customerType='all'):
        '''
            customerType == deep 深访
            customerType == all  所有顾客
        '''
        
        last_day,last_day = self.get_last_st_et_by_timeType(day, timeType='day')
        last_last_day,last_last_day = self.get_last_st_et_by_timeType(last_day, timeType='day')
        #last day 本周
        last_day_week_st,last_day_week_et = self.get_st_et_by_timeType(last_day, timeType='week',range='end')
        #last day 上周
        last_day_last_week_st,last_day_last_week_et = self.get_last_st_et_by_timeType(last_day, timeType='week',range='end')
        #last day 本月
        last_day_month_st,last_day_month_et = self.get_st_et_by_timeType(last_day, timeType='month',range='end')
        #last day 上月
        last_day_last_month_st,last_day_last_month_et = self.get_last_st_et_by_timeType(last_day, timeType='month',range='end')

        if customerType == 'out':
            #跳出 昨天进店量
            l_d_c,l_d_c_c  = myZhimindWsDatas.get_customer(startTime=last_day, endTime=last_day, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
            ws.cell(row=start_row,column=start_col).value = l_d_c
            return 
        
        if customerType=='deep':
            customerType='out'
        
        #前天进店量
        l_l_d_c,l_l_d_c_c  = myZhimindWsDatas.get_customer(startTime=last_last_day, endTime=last_last_day, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
        if customerType=='out':
            ws.cell(row=start_row,column=start_col).value = ws.cell(row=start_row,column=start_col-9).value - l_l_d_c
        else:
            ws.cell(row=start_row,column=start_col).value = l_l_d_c
        #昨天进店量
        l_d_c,l_d_c_c  = myZhimindWsDatas.get_customer(startTime=last_day, endTime=last_day, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
        if customerType=='out':
            ws.cell(row=start_row,column=start_col+1).value =ws.cell(row=start_row,column=start_col-8).value - l_d_c
        else:
            ws.cell(row=start_row,column=start_col+1).value = l_d_c
    
        #上周同期进店量
        l_d_l_w_c,l_d_l_w_c_c = myZhimindWsDatas.get_customer(startTime=last_day_last_week_st, endTime=last_day_last_week_et, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
        if customerType=='out':
            ws.cell(row=start_row,column=start_col+3).value = ws.cell(row=start_row,column=start_col-6).value -l_d_l_w_c
        else:
            ws.cell(row=start_row,column=start_col+3).value = l_d_l_w_c
        #本周累计进店量
        l_d_w_c,l_d_w_c_c = myZhimindWsDatas.get_customer(startTime=last_day_week_st, endTime=last_day_week_et, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
        if customerType=='out':
            ws.cell(row=start_row,column=start_col+4).value = ws.cell(row=start_row,column=start_col-5).value -l_d_w_c
        else:
            ws.cell(row=start_row,column=start_col+4).value = l_d_w_c
        #上月同期进店量
        l_d_l_m_c,l_d_l_m_c_c = myZhimindWsDatas.get_customer(startTime=last_day_last_month_st, endTime=last_day_last_month_et, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
        if customerType=='out':
            ws.cell(row=start_row,column=start_col+6).value = ws.cell(row=start_row,column=start_col-3).value -l_d_l_m_c
        else:
            ws.cell(row=start_row,column=start_col+6).value = l_d_l_m_c
        #当月累计进店量
        l_d_m_c,l_d_m_c_c = myZhimindWsDatas.get_customer(startTime=last_day_month_st, endTime=last_day_month_et, group=group, type='customer_flow', timeType='all', groupType=groupType,customerType=customerType)
        if customerType=='out':
            ws.cell(row=start_row,column=start_col+7).value = ws.cell(row=start_row,column=start_col-2).value -l_d_m_c
        else:
            ws.cell(row=start_row,column=start_col+7).value = l_d_m_c

        
                
    
    
    def analyze_template_file_month_report_template(self,name='month_report_template',day=None,export_file=''):
        last_day,last_day = self.get_last_st_et_by_timeType(day, timeType='day')
        last_last_day,last_last_day = self.get_last_st_et_by_timeType(last_day, timeType='day')
        #last day 本周
        last_day_week_st,last_day_week_et = self.get_st_et_by_timeType(last_day, timeType='week',range='end')
        #last day 上周
        last_day_last_week_st,last_day_last_week_et = self.get_last_st_et_by_timeType(last_day, timeType='week',range='end')
        #last day 本月
        last_day_month_st,last_day_month_et = self.get_st_et_by_timeType(last_day, timeType='month',range='end')
        #last day 上月
        
        last_day_last_month_st,last_day_last_month_et = self.get_last_st_et_by_timeType(last_day_month_st, timeType='month',range='start')
        
        
        logging.info( 'last_day_month st et:%s %s'%(last_day_month_st,last_day_month_et))
        logging.info( 'last_day_last_month st et:%s %s'%(last_day_last_month_st,last_day_last_month_et))
        #sys.exit()
        xfile = myLocalConfig.rules[name]['template_file']
        wb =        openpyxl.load_workbook(xfile)  
        comp_group = myLocalConfig.rules[name]['comp_group']
        comp_id = myRetailDatas.get_comp_id(comp_group=comp_group)
        for sheet_name in wb.get_sheet_names():
            ws = wb.get_sheet_by_name(sheet_name)  
            if sheet_name == u'核心数据':
#                 start_col = 3
#                 start_row = 23
                start_col = self.sheetConfig['sheet1']['col']
                start_row = self.sheetConfig['sheet1']['row']
                ws.cell(row=1,column=1).value = '%s（%s月）'%(self.sheetConfig['sheet1']['name'],last_day[5:7])
            elif sheet_name == u'门店数据':
#                 start_col = 6
#                 start_row = 5
                start_col = self.sheetConfig['sheet2']['col']
                start_row = self.sheetConfig['sheet2']['row']
                ws.cell(row=1,column=1).value = '%s（%s月）'%(self.sheetConfig['sheet2']['name'],last_day[5:7])
            else:continue
            ws.cell(row=2,column=start_col+8).value = '%s月进店次数'%(int(last_day[5:7]))
            ws.cell(row=2,column=start_col+12).value = '%s月进店情况'%(int(last_day[5:7]))
            
            ws.cell(row=3,column=start_col).value = '%s月进店量'%(int(last_day_last_month_st[5:7]))
            ws.cell(row=3,column=start_col+1).value = '%s月进店量'%(int(last_day[5:7]))
            ws.cell(row=3,column=start_col+3).value = '%s月进店量'%(int(last_day_last_month_st[5:7]))
            ws.cell(row=3,column=start_col+4).value = '%s月进店量'%(int(last_day[5:7]))
            ws.cell(row=3,column=start_col+6).value = '%s月进店量'%(int(last_day[5:7]))
            
            
            #break
            rows = ws.rows
            for i in range(len(rows)):
                if i<start_row-1 :continue
                if i>start_row+2 and self.testing==True :continue
                
                row = rows[i]
                group_type = row[start_col-3].value
                group_name = row[start_col-2].value
                
                if group_name==None:continue
                if group_type==None or group_type == u'省份' :
                    groupType = 1
                
                elif re.search('C[\d]+',group_type):
                    groupType = 4
                else:
                    groupType = 1
                #print group_name,group_type 
                group = myRetailDatas.get_group_id(name=group_name, comp_id=comp_id)
                logging.info('Group Name:%s Group Id:%s'%(group_name,group))
                #整体进店情况
                self.get_customer_info_for_month_report_template(ws,start_row=i+1, start_col=start_col, day=day, group=group, groupType=groupType, 
 customerType='all')

                #有效客户（入店时长大于40分钟）
                self.get_customer_info_for_month_report_template(ws,start_row=i+1, start_col=start_col+3, day=day, group=group, groupType=groupType, 
 customerType='deep')
                
                #跳出 昨天进店量
                self.get_customer_info_for_month_report_template(ws,start_row=i+1, start_col=start_col+6, day=day, group=group, groupType=groupType, 
 customerType='out')
                #当月进店次数 
                self.get_customer_month_freq(ws, start_row=i+1, start_col=start_col+8, day=last_day, comp_group=comp_group, group=group,groupType=groupType)
                
                #当月进店情况
                self.get_customer_month_week_data(ws, start_row=i+1, start_col=start_col+12, day=last_day, comp_group=comp_group, group=group,groupType=groupType)
        wb.save(export_file)  
    
    
    def analyze_template_file_day_report_template(self,name='tencent_3',day=None,export_file=''):
        last_day,last_day = self.get_last_st_et_by_timeType(day, timeType='day')
        last_last_day,last_last_day = self.get_last_st_et_by_timeType(last_day, timeType='day')
        #last day 本周
        last_day_week_st,last_day_week_et = self.get_st_et_by_timeType(last_day, timeType='week',range='end')
        #last day 上周
        last_day_last_week_st,last_day_last_week_et = self.get_last_st_et_by_timeType(last_day, timeType='week',range='end')
        #last day 本月
        last_day_month_st,last_day_month_et = self.get_st_et_by_timeType(last_day, timeType='month',range='end')
        #last day 上月
        last_day_last_month_st,last_day_last_month_et = self.get_last_st_et_by_timeType(last_day, timeType='month',range='end')
        
        logging.info( 'last_day:%s'%last_day)
        logging.info( 'last_last_day:%s'%last_last_day)
        logging.info( 'last_day_week st et:%s %s'%(last_day_week_st,last_day_week_et))
        logging.info( 'last_day_last_week st et:%s %s'%(last_day_last_week_st,last_day_last_week_et))
        logging.info( 'last_day_month st et:%s %s'%(last_day_month_st,last_day_month_et))
        logging.info( 'last_day_last_month st et:%s %s'%(last_day_last_month_st,last_day_last_month_et))
        
        xfile = myLocalConfig.rules[name]['template_file']
        wb =        openpyxl.load_workbook(xfile)  
        comp_group = myLocalConfig.rules[name]['comp_group']
        comp_id = myRetailDatas.get_comp_id(comp_group=comp_group)
        for sheet_name in wb.get_sheet_names():
            ws = wb.get_sheet_by_name(sheet_name)  
            if sheet_name == u'核心数据':
#                 start_col = 3
#                 start_row = 23
                start_col = self.sheetConfig['sheet1']['col']
                start_row = self.sheetConfig['sheet1']['row']
                ws.cell(row=1,column=1).value = '%s（%s月%s日）'%(self.sheetConfig['sheet1']['name'],last_day[5:7],last_day[8:10])
            elif sheet_name == u'门店数据':
#                 start_col = 6
#                 start_row = 5
                start_col = self.sheetConfig['sheet2']['col']
                start_row = self.sheetConfig['sheet2']['row']
                ws.cell(row=1,column=1).value = '%s（%s月%s日）'%(self.sheetConfig['sheet2']['name'],last_day[5:7],last_day[8:10])
            else:continue
            ws.cell(row=3,column=start_col).value = '%s日进店量'%(int(last_last_day[8:10]))
            ws.cell(row=3,column=start_col+1).value = '%s日进店量'%(int(last_day[8:10]))
            ws.cell(row=3,column=start_col+9).value = '%s日进店量'%(int(last_last_day[8:10]))
            ws.cell(row=3,column=start_col+10).value = '%s日进店量'%(int(last_day[8:10]))
            ws.cell(row=3,column=start_col+18).value = '%s日进店量'%(int(last_day[8:10]))
            #break
            rows = ws.rows
            for i in range(len(rows)):
                if i<start_row-1 :continue
                if i>start_row+2 and self.testing==True :continue
                
                row = rows[i]
                group_type = row[start_col-3].value
                group_name = row[start_col-2].value
                
                if group_name==None:continue
                if group_type==None or group_type == u'省份' :
                    groupType = 1
                
                elif re.search('C[\d]+|V[\d]+',group_type):
                    groupType = 4
                else:
                    groupType = 1
                #print group_name,group_type 
                group = myRetailDatas.get_group_id(name=group_name, comp_id=comp_id)
                logging.info('Group Name:%s Group Id:%s'%(group_name,group))
                #整体进店情况
                self.get_customer_info_for_3(ws,start_row=i+1, start_col=start_col, day=day, group=group, groupType=groupType, 
 customerType='all')
                #有效客户（入店时长大于40分钟）
                self.get_customer_info_for_3(ws,start_row=i+1, start_col=start_col+9, day=day, group=group, groupType=groupType, 
 customerType='deep')
                
                #跳出 昨天进店量
                self.get_customer_info_for_3(ws,start_row=i+1, start_col=start_col+18, day=day, group=group, groupType=groupType, 
 customerType='out')
                #当月进店次数 
                self.get_customer_month_freq(ws, start_row=i+1, start_col=start_col+20, day=day, comp_group=comp_group, group=group,groupType=groupType)
                
                #当月进店情况
                self.get_customer_month_week_data(ws, start_row=i+1, start_col=start_col+24, day=day, comp_group=comp_group, group=group,groupType=groupType)
        wb.save(export_file)        
    


    def main(self,day=None,rule=None):
        if day==None:
            #day = (datetime.date.today()-datetime.timedelta(1)).strftime("%Y-%m-%d")
            day = datetime.date.today().strftime("%Y-%m-%d")
        yday = (datetime.date(int(day[:4]),int(day[5:7]),int(day[8:10]))-datetime.timedelta(1)).strftime("%Y-%m-%d")
        
        #day = "2015-01-01"
        if rule == None:
            name = 'tencent_3'
        else:
            name = rule
        
        send_status = False
        
        mail_user = baseLocalConfig.emailConfig['user']
        mail_pass = baseLocalConfig.emailConfig['passwd']
        mail_stmp = baseLocalConfig.emailConfig['stmp']
        mail_postfix = baseLocalConfig.emailConfig['postfix']
        
        #print  myLocalConfig.rules
        
        if name not in myLocalConfig.rules.keys():
            logging.error('rule name :%s not exists in rule file.'%name)
        to_list = myLocalConfig.rules[name]['mail_to_list']
        sheetConfig = myLocalConfig.rules[name]['sheet_config_dict']
        self.sheetConfig = sheetConfig
        
        comp_name =  myLocalConfig.rules[name]['name']
        export_path = os.path.join(current_dir,myLocalConfig.rules[name]['export_path'])
            #sub = "WIFI数据表格-每日查询反馈"
        if re.search('month_report',rule):
            sub = 'Wifi Data Report(Month)'
            content = "%s  %s"%(comp_name,yday[:7])
        else:
            sub = 'Wifi Data Report'
            content = "%s  %s"%(comp_name,day)
            
    
        if re.search('month',rule):
            export_file = os.path.join(current_dir,"%s/%s(%s).xlsx"%(export_path,comp_name,yday[:7]))
        else:
            export_file = os.path.join(current_dir,"%s/%s(%s).xlsx"%(export_path,comp_name,yday))
    
        if  os.path.exists(export_file):
            try:
                os.popen('rm -rf "%s"'%export_file) 
            except:pass
        for i in range(100):
            if send_status==True:break
            if i!=0:
                logging.info("start to try %s times"%i)
    
            if not os.path.exists(export_file):
                myExportData.analyze_template_file(name=name,day=day,export_file=export_file)
            else:
                logging.info("export file %s already exists."%export_file)
            if i!=0:
                logging.info("start  try %s times to send mail"%i)
            if testing==True:continue
            atts = [export_file]
            ret = send_mail_with_atts(mail_user,mail_pass,mail_stmp,mail_postfix,to_list,sub,content,atts=atts)
            if ret==True:
                send_status = True
                logging.info("send mail successed.")
            else:
                logging.info("send mail faild %s times."%i)
            time.sleep(5)

if __name__ == '__main__':
    from localConfig import myLocalConfig
    import argparse,re,datetime,time
    
    parser = argparse.ArgumentParser(description='args')
    parser.add_argument('--day',metavar=u"day",default=None)
    parser.add_argument('--rule',metavar=u"rule",default=None)
    parser.add_argument('--testing',action='store_true',help='testing')
    parser.add_argument('--log_level',metavar='logging level',default='info')
    args = parser.parse_args()
    
    day = args.day
    rule = args.rule
    testing = args.testing
    log_level = args.log_level

    from base.mylogging import logging_stdout_and_logfile
    #logfile = os.path.join(current_dir,'../../log/export_day_data.log')
    logfile = '/data/log/export_data.log'
    logging = logging_stdout_and_logfile(level=log_level,
                                         logfile=logfile,maxBytes=100*1024*1024)

    myExportData = ExportData(name=rule,testing=testing)
    myExportData.main(day=day,rule=rule)
            
  